package com.cj.image.mapper;

import com.cj.image.bean.ImageTableBean;
import com.cj.image.bean.ImageTagBean;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ImageMapper {

    // 根据 ID 查询图片表数据，并支持分页
    @Select("select * from image_table where id = #{image_id}")
    List<ImageTableBean> getImageTableById(String image_id);

    // 根据 tag_id 查询图片表数据，并支持分页
    @Select("select * from image_table where id in (select image_id from image_tag where tag_id = #{tag_id})")
    List<ImageTableBean> getImageTables(String tag_id);

    // 支持标签递归查询的图片表数据，并支持分页
    @Select("select * from image_table where id in (select image_id from image_tag where tag_id = #{tag_id} or tag_id in (select tag_id from tag_info where parent_tag_id = #{tag_id}) or tag_id in (select tag_id from tag_info where parent_tag_id in (select tag_id from tag_info where parent_tag_id = #{tag_id})))")
    List<ImageTableBean> getImageTablesLoop(String image_id);

    // 获取所有文件月份，并支持分页
    @Select("select distinct file_month from image_table order by file_month desc")
    List<String> getAllFileMonth();

    @Select("SELECT * FROM image_table WHERE file_month = #{fileMonth}")
    List<ImageTableBean> getImageTableByFileMonth(@Param("fileMonth") String fileMonth);

    @Insert("INSERT INTO image.image_table(file_name, file_path, file_month, file_date) " +
            "VALUES(#{file_name}, #{file_path}, #{file_month}, #{file_date}) " +
            "ON DUPLICATE KEY UPDATE " +
            "file_month = VALUES(file_month), file_date = VALUES(file_date)")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insertOrUpdateImageTable(ImageTableBean imageTableBean);

    /**
     * 通过 file_name 和 file_path 查询记录
     */
    @Select("SELECT id, file_name, file_path, file_month, file_date " +
            "FROM image.image_table " +
            "WHERE file_name = #{file_name} AND file_path = #{file_path}")
    ImageTableBean selectByFileNameAndPath(@Param("file_name") String fileName, @Param("file_path") String filePath);

    /**
     * 删除除了最小 id 外的所有行
     */
    @Delete("DELETE FROM image.image_table WHERE id NOT IN (SELECT MIN(id) FROM image.image_table WHERE file_name = #{file_name} AND file_path = #{file_path})")
    void deleteAllExceptMinId(@Param("file_name") String fileName, @Param("file_path") String filePath);
}

